package com.vue.cn.utils.excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Component
public class ExcelUtils {
    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private DecimalFormat df = new DecimalFormat("0.00");

    public <T> Workbook listToExcel(List<T> all, String sheetName) throws IllegalAccessException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(sheetName);

        //样式设置
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 15);
        font.setFontName("华文行楷");
        font.setBold(true);
        cellStyle.setFont(font);


        //打印标题的信息
        if (all.size() == 0) {
            return workbook;
        }
        Class<?> clazz = all.get(0).getClass();
        Field[] fields1 = clazz.getDeclaredFields();

        Row titleRow = sheet.createRow(0);
        titleRow.setHeightInPoints(20);

        for (int i = 0; i < fields1.length; i++) {
            Cell cell = titleRow.createCell(i);

            sheet.setColumnWidth(i, 20 * 256);
            cell.setCellStyle(cellStyle);

            boolean flag = fields1[i].isAccessible();
            fields1[i].setAccessible(true);
            String name = fields1[i].getName();
            cell.setCellValue(name);
            fields1[i].setAccessible(flag);
        }

        //打印每个员工的信息
        for (int i = 0; i < all.size(); i++) {
            T t = all.get(i);
            Row row = sheet.createRow(i + 1);
            row.setHeightInPoints(20);

            Class<?> aClass = t.getClass();
            Field[] fields = aClass.getDeclaredFields();

            for (int j = 0; j < fields.length; j++) {
                boolean flag = fields[j].isAccessible();
                Cell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);

                fields[j].setAccessible(true);
                System.out.println(fields[j].getType());
                Object value = fields[j].get(t);
                Class<?> type = fields[j].getType();

                try {
                    setCellValue(type, value, cell);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                fields[i].setAccessible(flag);
            }
        }
        return workbook;


    }

    private void setCellValue(Class<?> type, Object value, Cell cell) throws ParseException {
        switch (type.getName()) {
            case "java.lang.String":
                cell.setCellValue((String) value);
                return;
            case "java.lang.Integer":
                cell.setCellValue((Integer) value);
                return;
            case "java.lang.Double":
                cell.setCellValue(df.format((double) value));
                return;
            case "java.lang.Boolean":
                cell.setCellValue((boolean) value);
                return;
            case "java.util.Date":
                String format = sdf.format((Date) value);
                cell.setCellValue(format);
                return;
        }

    }
    
    public <T> List<T> excelToList(Workbook workbook,Class<T> clazz) throws Exception {
        
        Sheet sheetAt = workbook.getSheetAt(0);
        int lastRowNum = sheetAt.getLastRowNum();
        ArrayList<T> list = new ArrayList<>();

        //反射获取employee的成员变量类型与构造方法
        Field[] fields = clazz.getDeclaredFields();
        String[] fieldName = new String[fields.length];
        Class[] fieldType = new Class[fields.length];
        for (int i = 0; i < fields.length; i++) {
            boolean flag = fields[i].isAccessible();
            fields[i].setAccessible(true);
            fieldName[i] = fields[i].getName();
            fieldType[i] = fields[i].getType();
            fields[i].setAccessible(flag);
        }
        Constructor constructor = clazz.<T>getConstructor();

        for (int i = 1; i < lastRowNum + 1; i++) {
            Row row = sheetAt.getRow(i);
            T t = (T) constructor.newInstance();
            //获单元格数据
            short lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                if (cell != null) {
                    setValue(cell, fieldName[j], fieldType[j], t);

                }
            }
            list.add(t);
        }
        
        return list;
    }

    private <T> void setValue(Cell cell, String fieldName, Class clazz, T t) throws Exception {
        System.out.println(cell.getCellTypeEnum());
        System.out.println(clazz);
        System.out.println(fieldName);
        //得到属性描述器
        PropertyDescriptor propertyDescriptor = new PropertyDescriptor(fieldName, t.getClass());
        Method writeMethod = propertyDescriptor.getWriteMethod();

        switch (clazz.getName()) {
            case "java.util.Date":
                writeMethod.invoke(t, sdf.parse(cell.getStringCellValue()));
                break;
            case "java.lang.String":
                writeMethod.invoke(t, cell.getStringCellValue());
                break;
            case "java.lang.Double":
                System.out.println(cell.getStringCellValue());
                writeMethod.invoke(t, Double.parseDouble(cell.getStringCellValue()));
                break;
            case "java.lang.Integer":
                Double value = cell.getNumericCellValue();
                writeMethod.invoke(t, value.intValue());
                break;
            case "java.lang.Boolean":
                writeMethod.invoke(t, cell.getBooleanCellValue());
                break;
        }

    }

}
